

/**
	AUTHOR		: GANDHI
	DATE		: 14 July, 2005
	DESCRIPTION	: THIS PROC IS USED TO GET FORECAST,BACKLOG AND SHIPPING DETAILS BASED ON SEARCH CRITERIA. 
		
	EXEC SP_NEW_SALES_FORECAST_BACKLOG_REPORT 0, 200, 0, 3, 2010, 'byProduct', '0', -1.0, 'byMonth', 9, 3, 0
	EXEC SP_NEW_SALES_FORECAST_BACKLOG_REPORT 0, -1, 0, 3, 2010, 'byProduct', '0', -1.0, 'ByQuarter', 6, 3, 0
*/

			

ALTER          PROC SP_NEW_SALES_FORECAST_BACKLOG_REPORT(
 
 @ODM_INPUT VARCHAR(100), 
 @PRODUCT_INPUT VARCHAR(100),
 @SPERSON_INPUT VARCHAR(100),
 @QUARTER INT,		--1 or 2 or 3 or 4
 @YEAR INT, 
 @TYPE VARCHAR(20),	--BYPRODUCT  OR  BYENDCUST
 @defaultPrompt varchar(20),
 @CONF_LEVEL FLOAT,
 @PERIOD VARCHAR(20),	--byMonth  or  byQuarter
 @FROM_MONTH INT,	--starts from Zero(0 to 11)
 @SHOW_NEXT INT, 		--No of months to be shown
 @CUSTOMER_INPUT VARCHAR(100) )


 
 AS
 BEGIN
	
	
	DECLARE @RESULTS TABLE( BUILDNO VARCHAR(50), SPERSON_ID INT, CUSTOMER VARCHAR(100), PROJECT  VARCHAR(100), PRODUCT VARCHAR(100), ODM VARCHAR(100), MONTH INT, QUARTER INT, YEAR INT, FC_QUANTITY INT, FC_PRICE NUMERIC(18,4) , USERLIST1 VARCHAR(100), USERDATE1 VARCHAR(100), USERTXT1 VARCHAR(100), USERTXT2 VARCHAR(100), CONFIDENCE_LEVEL FLOAT, BL_QUANTITY INT, BL_PRICE NUMERIC(18,4), ACTUAL_QUANTITY INT, ACTUAL_PRICE NUMERIC(18,4), CUSTID INT, PROJID INT, PRODID INT, ODMID INT, FORECAST_TYPE VARCHAR(50), SALES_PERSON VARCHAR(100), QUARTER_START INT, QUARTER_END INT  )
	DECLARE @SPERSON_RESULTS TABLE( BUILDNO VARCHAR(50), SPERSON_ID INT, CUSTOMER VARCHAR(100), PROJECT  VARCHAR(100), PRODUCT VARCHAR(100), ODM VARCHAR(100), MONTH INT, QUARTER INT, YEAR INT, FC_QUANTITY INT, FC_PRICE NUMERIC(18,4) , USERLIST1 VARCHAR(100), USERDATE1 VARCHAR(100), USERTXT1 VARCHAR(100), USERTXT2 VARCHAR(100), CONFIDENCE_LEVEL FLOAT, BL_QUANTITY INT, BL_PRICE NUMERIC(18,4), ACTUAL_QUANTITY INT, ACTUAL_PRICE NUMERIC(18,4), CUSTID INT, PROJID INT, PRODID INT, ODMID INT, FORECAST_TYPE VARCHAR(50), SALES_PERSON VARCHAR(100), QUARTER_START INT, QUARTER_END INT )
	DECLARE @BL_RESULTS TABLE( SPERSON_ID INT, CUSTOMER VARCHAR(100), PROJECT  VARCHAR(100), PRODUCT VARCHAR(100), ODM VARCHAR(100), MONTH INT, QUARTER INT, YEAR INT, BL_QUANTITY INT, BL_PRICE NUMERIC(18,4) , USERLIST1 VARCHAR(100), USERDATE1 VARCHAR(100), USERTXT1 VARCHAR(100), USERTXT2 VARCHAR(100), CONFIDENCE_LEVEL FLOAT, SALES_PERSON VARCHAR(100), QUARTER_START INT, QUARTER_END INT )	
	DECLARE @BL_TEMP_RESULTS TABLE( SPERSON_ID INT, CUSTOMER VARCHAR(100), PROJECT  VARCHAR(100), PRODUCT VARCHAR(100), ODM VARCHAR(100), MONTH INT, QUARTER INT, YEAR INT, BL_QUANTITY INT, BL_PRICE NUMERIC(18,4) , USERLIST1 VARCHAR(100), USERDATE1 VARCHAR(100), USERTXT1 VARCHAR(100), USERTXT2 VARCHAR(100), CONFIDENCE_LEVEL FLOAT, SALES_PERSON VARCHAR(100), QUARTER_START INT, QUARTER_END INT )
	DECLARE @SHIP_RESULTS TABLE( SPERSON_ID INT, CUSTOMER VARCHAR(100), PROJECT  VARCHAR(100), PRODUCT VARCHAR(100), ODM VARCHAR(100), MONTH INT, QUARTER INT, YEAR INT, ACTUAL_QUANTITY INT, ACTUAL_PRICE NUMERIC(18,4) , USERLIST1 VARCHAR(100), USERDATE1 VARCHAR(100), USERTXT1 VARCHAR(100), USERTXT2 VARCHAR(100), CONFIDENCE_LEVEL FLOAT, SALES_PERSON VARCHAR(100), QUARTER_START INT, QUARTER_END INT )	
	DECLARE @SHIP_TEMP_RESULTS TABLE( SPERSON_ID INT, CUSTOMER VARCHAR(100), PROJECT  VARCHAR(100), PRODUCT VARCHAR(100), ODM VARCHAR(100), MONTH INT, QUARTER INT, YEAR INT, ACTUAL_QUANTITY INT, ACTUAL_PRICE NUMERIC(18,4) , USERLIST1 VARCHAR(100), USERDATE1 VARCHAR(100), USERTXT1 VARCHAR(100), USERTXT2 VARCHAR(100), CONFIDENCE_LEVEL FLOAT, SALES_PERSON VARCHAR(100), QUARTER_START INT, QUARTER_END INT )	
	DECLARE @TOTAL_RESULTS TABLE( SPERSON_ID INT, CUSTOMER VARCHAR(100), PROJECT  VARCHAR(100), PRODUCT VARCHAR(100), ODM VARCHAR(100), MONTH INT, QUARTER INT, YEAR INT, FC_QUANTITY INT, FC_PRICE NUMERIC(18,4), CONFIDENCE_LEVEL FLOAT, CUSTID INT, PROJID INT, PRODID INT, ODMID INT, FORECAST_TYPE VARCHAR(50), SALES_PERSON VARCHAR(100), QUARTER_START INT, QUARTER_END INT )	
	DECLARE @TEMP_RESULTS TABLE( SPERSON_ID INT, CUSTOMER VARCHAR(100), PROJECT  VARCHAR(100), PRODUCT VARCHAR(100), ODM VARCHAR(100), MONTH INT, QUARTER INT, YEAR INT, FC_QUANTITY INT, FC_PRICE NUMERIC(18,4), CONFIDENCE_LEVEL FLOAT, CUSTID INT, PROJID INT, PRODID INT, ODMID INT, FORECAST_TYPE VARCHAR(50), SALES_PERSON VARCHAR(100), QUARTER_START INT, QUARTER_END INT )
	CREATE TABLE #CHIPSET_PARTS_TABLE ( PRODUCT_ID INT, PART_NUMBER VARCHAR(100) )	

	DECLARE @START_MONTH INT 
	DECLARE @START_DATE DATETIME
	DECLARE @END_DATE DATETIME


	IF @PERIOD IS NOT NULL AND LTRIM(RTRIM(@PERIOD)) = 'byQuarter'
	BEGIN
		IF @QUARTER = 1
		BEGIN
			SET @START_MONTH = 0
		END
		ELSE IF @QUARTER = 2
		BEGIN
			SET @START_MONTH = 3
		END
		ELSE IF @QUARTER = 3
		BEGIN
			SET @START_MONTH = 6
		END
		ELSE IF @QUARTER = 4
		BEGIN
			SET @START_MONTH = 9
		END
		ELSE
		BEGIN
			SET @START_MONTH = 0
		END

		SET @START_DATE = CONVERT(DATETIME, CONVERT( VARCHAR, @START_MONTH+1 )+'/01/'+ CONVERT( VARCHAR, @YEAR ))
		SET @END_DATE	= DATEADD( mi, -1 , DATEADD( m, 3, CONVERT(DATETIME, @START_DATE ) ) )
	END
	ELSE IF @PERIOD IS NOT NULL AND LTRIM(RTRIM(@PERIOD)) = 'byMonth'
	BEGIN
		SET @START_MONTH   = 	@FROM_MONTH				
		
		SET @START_DATE = CONVERT(DATETIME, CONVERT( VARCHAR, @START_MONTH+1 )+'/01/'+ CONVERT( VARCHAR, @YEAR ))
		SET @END_DATE	= DATEADD( mi, -1 , DATEADD( m, @SHOW_NEXT, CONVERT(DATETIME, @START_DATE ) ) )		
	END

	--PRINT '@START_DATE='+CONVERT(VARCHAR,@START_DATE)
	--PRINT '@END_DATE='+CONVERT(VARCHAR,@END_DATE)

	DECLARE @SF_SPID INT
	DECLARE @SF_SPERSON_NAME VARCHAR(100)
	DECLARE @SF_CUSTOMER VARCHAR(100)
 	DECLARE @SF_PRODUCT VARCHAR(50)
 	DECLARE @SF_PROJECT VARCHAR(50)
 	DECLARE @SF_ODM VARCHAR(100)
	DECLARE @SF_CUSTOMER_NAME VARCHAR(100)
 	DECLARE @SF_PRODUCT_NAME VARCHAR(50)
 	DECLARE @SF_PROJECT_NAME VARCHAR(50)
 	DECLARE @SF_ODM_NAME VARCHAR(100)
 	DECLARE @SF_MONTH INT
	DECLARE @SF_QUARTER INT
 	DECLARE @SF_YEAR INT
 	DECLARE @SF_QUANTITY INT
	DECLARE @SF_PRICE FLOAT
 	DECLARE @SF_USERLIST1 VARCHAR(100)
	DECLARE @SF_USERDATE1 VARCHAR(100)
	DECLARE @SF_USERTXT1 VARCHAR(100)
	DECLARE @SF_USERTXT2 VARCHAR(100)
	DECLARE @SF_CONFIDENCE_LEVEL FLOAT
	DECLARE @MKTG_PART_NUMBER VARCHAR(100)
	
	DECLARE @SPERSON_SPID INT
	DECLARE @LEVEL_ORDER INT
	DECLARE @SF_FORECAST_TYPE VARCHAR(50)
	DECLARE @SF_BUILDNO VARCHAR(50)
	DECLARE @QUARTER_START INT
	DECLARE @QUARTER_END INT

 	DECLARE @SPERSON_CURSOR CURSOR 
	DECLARE @PRIMARY_PART_NUMBER VARCHAR(50)
	DECLARE @PROCESSSTEP_ID INT
	DECLARE @CHIPSET_UNIT_PRICE NUMERIC(18,4)

	--DECLARE @CUSTOMER_INPUT VARCHAR(100)
	DECLARE @PROJECT_INPUT VARCHAR(100)
	--SET @CUSTOMER_INPUT = NULL
	SET @PROJECT_INPUT = NULL

	IF @CUSTOMER_INPUT IS NULL  OR  LTRIM(RTRIM(@CUSTOMER_INPUT)) = ''  OR  LTRIM(RTRIM(@CUSTOMER_INPUT)) = '-1'  OR  LTRIM(RTRIM(@CUSTOMER_INPUT)) = '-2' 
	BEGIN	
		SET @CUSTOMER_INPUT = '0'
	END
	IF @PROJECT_INPUT IS NULL  OR  LTRIM(RTRIM(@PROJECT_INPUT)) = ''  OR  LTRIM(RTRIM(@PROJECT_INPUT)) = '-1'  OR  LTRIM(RTRIM(@PROJECT_INPUT)) = '-2'
	BEGIN	
		SET @PROJECT_INPUT = '0'
	END
	IF @PRODUCT_INPUT IS NULL  OR  LTRIM(RTRIM(@PRODUCT_INPUT)) = ''  OR  LTRIM(RTRIM(@PRODUCT_INPUT)) = '-1'  OR  LTRIM(RTRIM(@PRODUCT_INPUT)) = '-2'
	BEGIN
		SET @PRODUCT_INPUT = '0'
	END
	IF @ODM_INPUT IS NULL  OR  LTRIM(RTRIM(@ODM_INPUT)) = ''  OR  LTRIM(RTRIM(@ODM_INPUT)) = '-1'  OR  LTRIM(RTRIM(@ODM_INPUT)) = '-2'
	BEGIN
		SET @ODM_INPUT = '0'
	END	
	IF @SPERSON_INPUT IS NULL  OR  LTRIM(RTRIM(@SPERSON_INPUT)) = ''  OR  LTRIM(RTRIM(@SPERSON_INPUT)) = '-1'  OR  LTRIM(RTRIM(@SPERSON_INPUT)) = '-2'
	BEGIN
		SET @SPERSON_INPUT = '0'
	END	

	DECLARE @PART_NUMBER VARCHAR(100)
	DECLARE @END_CUSTOMER VARCHAR(100)
	DECLARE @SALES_PERSON VARCHAR(100)
	DECLARE @CUSTOMER VARCHAR(100)
	IF @PRODUCT_INPUT <> '0'
	BEGIN		
		IF((SELECT COUNT(*) FROM PRODUCT P, PROCESS_PARAMETER PP WHERE P.PRODUCT_ID = @PRODUCT_INPUT AND PP.PROCESSSTEP_ID = P.PROCESSSTEP_ID AND PARAMETER_KEY = 'IsChipset' AND PARAMETER_VALUE = 'true') > 0 )
		 BEGIN
			SELECT @PROCESSSTEP_ID = PROCESSSTEP_ID FROM PRODUCT WHERE PRODUCT_ID = @PRODUCT_INPUT
			--for a chipset part number we need to consider the primary part number for backlog and shipped qty's
			--SELECT @PART_NUMBER = P.PART_NUMBER FROM Assembly_BOM_Setup_Table ABST, PRODUCT P WHERE ABST.ASSEMBLY_PROCESSSTEP_ID = @PROCESSSTEP_ID AND ABST.IS_PRIMARY_PART = 0 AND P.PROCESSSTEP_ID = ABST.BOM_PROCESSSTEP_ID
			INSERT INTO #CHIPSET_PARTS_TABLE(PRODUCT_ID, PART_NUMBER) SELECT P.PRODUCT_ID, P.PART_NUMBER FROM Assembly_BOM_Setup_Table ABST, PRODUCT P WHERE ABST.ASSEMBLY_PROCESSSTEP_ID = @PROCESSSTEP_ID AND P.PROCESSSTEP_ID = ABST.BOM_PROCESSSTEP_ID
		 END
		ELSE
		 BEGIN
			--SELECT @PART_NUMBER = PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = @PRODUCT_INPUT
			INSERT INTO #CHIPSET_PARTS_TABLE(PRODUCT_ID, PART_NUMBER) SELECT PRODUCT_ID, PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = @PRODUCT_INPUT
		 END
	END
	IF @ODM_INPUT <> '0'
	BEGIN
		SELECT @END_CUSTOMER = C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = @ODM_INPUT
	END
	IF @SPERSON_INPUT <> '0'
	BEGIN
		SELECT @SALES_PERSON = SALESPERSON_NAME FROM SALES_PERSONS WHERE SALESPERSON_ID = @SPERSON_INPUT
	END
	IF @CUSTOMER_INPUT <> '0'
	BEGIN
		SELECT @CUSTOMER = C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = @CUSTOMER_INPUT
	END

	DECLARE @REPORT_ON VARCHAR(10)
	SET @REPORT_ON = 'SALES'
	 	
 	DECLARE @SQLSTRING NVARCHAR(3000)
	DECLARE @PARAMDEFINITION NVARCHAR(500)

	IF @REPORT_ON IS NOT NULL AND LTRIM(RTRIM(@REPORT_ON)) = 'SALES'
	BEGIN
		
		SET @SQLSTRING = N'DECLARE FORECASTCURSOR CURSOR FOR SELECT NULL, SPERSONID, CUSTID, PROJID, PRODID, ODMID, MONTH, QUARTER, YEAR, QTY, QTY*PRICE, USERLIST1, USERDATE1, USERTXT1, USERTXT2, CONFIDENCE_LEVEL, ''SALES'' FROM FCENTRYMASTER WHERE ROWID IS NOT NULL '
		SET @PARAMDEFINITION = N'@PRM_PRODUCT_INPUT VARCHAR(100), @PRM_ODM_INPUT VARCHAR(100), @PRM_SPERSON_INPUT VARCHAR(100), @START_MONTH INT, @START_DATE DATETIME, @END_DATE DATETIME, @PRM_CUSTOMER_INPUT VARCHAR(100)'--, @PRM_CONF_LEVEL FLOAT'
		
		IF @PRODUCT_INPUT <> '0'
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND PRODID = @PRM_PRODUCT_INPUT '
		END
		IF @ODM_INPUT <> '0'
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND ODMID = @PRM_ODM_INPUT '
		END
		IF @SPERSON_INPUT <> '0'
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND SPERSONID = @PRM_SPERSON_INPUT '
		END	
		/*IF @CONF_LEVEL <> -1.0
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND CONFIDENCE_LEVEL = @PRM_CONF_LEVEL '
		END*/	
		IF @CUSTOMER_INPUT <> '0'
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND CUSTID = @PRM_CUSTOMER_INPUT '
		END	
		
		SET @SQLSTRING = @SQLSTRING + ' AND CONVERT(DATETIME, CONVERT( VARCHAR, MONTH+1 )+''/01/''+ CONVERT( VARCHAR, YEAR )) >= @START_DATE'
		SET @SQLSTRING = @SQLSTRING + ' AND CONVERT(DATETIME, CONVERT( VARCHAR, MONTH+1 )+''/01/''+ CONVERT( VARCHAR, YEAR )) <= @END_DATE'
		--PRINT @SQLSTRING
		EXECUTE SP_EXECUTESQL @SQLSTRING, @PARAMDEFINITION, @PRM_PRODUCT_INPUT = @PRODUCT_INPUT, @PRM_ODM_INPUT = @ODM_INPUT,  @PRM_SPERSON_INPUT = @SPERSON_INPUT, @START_MONTH = @START_MONTH, @START_DATE = @START_DATE, @END_DATE = @END_DATE, @PRM_CUSTOMER_INPUT = @CUSTOMER_INPUT--, @PRM_CONF_LEVEL = @CONF_LEVEL
	END

	
	SET @SQLSTRING = NULL
	SET @PARAMDEFINITION = NULL
	
	IF @REPORT_ON IS NOT NULL AND LTRIM(RTRIM(@REPORT_ON)) = 'SALES'
	BEGIN
		
		SET @SQLSTRING = N'DECLARE SPERSON_CURSOR CURSOR FOR SELECT DISTINCT FCSPERSONREL.SPERSONID, LEVELORDER FROM FCSPERSONREL WHERE 
				   FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE ROWID IS NOT NULL'
		SET @PARAMDEFINITION = N'@PRM_PRODUCT_INPUT VARCHAR(100), @PRM_ODM_INPUT VARCHAR(100), @PRM_SPERSON_INPUT VARCHAR(100), @PRM_CUSTOMER_INPUT VARCHAR(100)'
		
		IF @PRODUCT_INPUT <> '0'
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND PRODID = @PRM_PRODUCT_INPUT '
		END
		IF @ODM_INPUT <> '0'
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND ODMID = @PRM_ODM_INPUT '
		END
		IF @SPERSON_INPUT <> '0'
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND SPERSONID = @PRM_SPERSON_INPUT '
		END
		IF @CUSTOMER_INPUT <> '0'
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND CUSTID = @PRM_CUSTOMER_INPUT '
		END		
				
		SET @SQLSTRING = @SQLSTRING + ' ) ORDER BY LEVELORDER DESC, SPERSONID '	

		EXECUTE SP_EXECUTESQL @SQLSTRING, @PARAMDEFINITION, @PRM_PRODUCT_INPUT = @PRODUCT_INPUT, @PRM_ODM_INPUT = @ODM_INPUT,  @PRM_SPERSON_INPUT = @SPERSON_INPUT, @PRM_CUSTOMER_INPUT = @CUSTOMER_INPUT
	END

	DECLARE @PART_NUMBER_TYPE INT
	SELECT @PART_NUMBER_TYPE = PART_NUMBER_TYPE FROM SYSTEMPRFS			

	
		OPEN FORECASTCURSOR 
 			FETCH NEXT FROM FORECASTCURSOR
 			INTO @SF_BUILDNO, @SF_SPID, @SF_CUSTOMER, @SF_PROJECT, @SF_PRODUCT, @SF_ODM, @SF_MONTH, @SF_QUARTER, @SF_YEAR, @SF_QUANTITY, @SF_PRICE, @SF_USERLIST1, @SF_USERDATE1, @SF_USERTXT1, @SF_USERTXT2, @SF_CONFIDENCE_LEVEL, @SF_FORECAST_TYPE
 			WHILE @@FETCH_STATUS = 0
 			BEGIN

				SET @SF_CUSTOMER_NAME = NULL
				SET @SF_PROJECT_NAME = NULL
				SET @SF_PRODUCT_NAME = NULL
				SET @SF_ODM_NAME = NULL
				SET @SF_SPERSON_NAME = NULL
				SELECT @SF_CUSTOMER_NAME = C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = @SF_CUSTOMER
				SELECT @SF_PROJECT_NAME = PROJNAME FROM  PROJSETUP WHERE PROJID = @SF_PROJECT
				SELECT @SF_PRODUCT_NAME = PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = @SF_PRODUCT
				SELECT @SF_ODM_NAME = C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = @SF_ODM
				SELECT @SF_SPERSON_NAME = SALESPERSON_NAME FROM SALES_PERSONS WHERE SALESPERSON_ID = @SF_SPID

				IF (@SF_MONTH >=0 AND @SF_MONTH <=2)
	  			BEGIN
	  				SET @QUARTER_START = 1
	  				SET @QUARTER_END =3
	  			END
	  			ELSE IF (@SF_MONTH >=3 AND @SF_MONTH <=5)
	  			BEGIN
	  				SET @QUARTER_START = 2
	  				SET @QUARTER_END =6
	 			END
	  			ELSE IF (@SF_MONTH >=6 AND @SF_MONTH <=8)
	  			BEGIN
	  				SET @QUARTER_START = 3
	  				SET @QUARTER_END =9
	  			END						
	  			ELSE IF (@SF_MONTH >=9 AND @SF_MONTH <=11)
	  			BEGIN
	  				SET @QUARTER_START = 4
	  				SET @QUARTER_END =12
	   			END
 				
				INSERT INTO @RESULTS VALUES(  @SF_BUILDNO, @SF_SPID, @SF_CUSTOMER_NAME, @SF_PROJECT_NAME, @SF_PRODUCT_NAME, @SF_ODM_NAME, @SF_MONTH, @SF_QUARTER, @SF_YEAR, @SF_QUANTITY, @SF_PRICE, @SF_USERLIST1, @SF_USERDATE1, @SF_USERTXT1, @SF_USERTXT2, @SF_CONFIDENCE_LEVEL, NULL, NULL, NULL, NULL, @SF_CUSTOMER, @SF_PROJECT, @SF_PRODUCT, @SF_ODM, @SF_FORECAST_TYPE, @SF_SPERSON_NAME, @QUARTER_START, @QUARTER_END )
 					
				IF @PART_NUMBER_TYPE <> 1
				BEGIN		
					SELECT @MKTG_PART_NUMBER = PRODUCT.P_MARKETING_PART_NUMBER FROM PRODUCT,PROCESS_STEP  WHERE PRODUCT.PRODUCT_ID = @SF_PRODUCT AND PRODUCT.PROCESSSTEP_ID IN (SELECT PROCESSSTEP_ID FROM PROCESS_STEP WHERE PS_TYPE_NAME LIKE 'FINISHED GOODS') AND PRODUCT.PROCESSSTEP_ID=PROCESS_STEP.PROCESSSTEP_ID  
					UPDATE @RESULTS SET PRODUCT =  @MKTG_PART_NUMBER WHERE PRODUCT = @SF_PRODUCT_NAME
 				END

				SET @SF_BUILDNO = NULL
				SET @SF_SPID = NULL
				SET @SF_CUSTOMER = NULL
				SET @SF_PROJECT = NULL
				SET @SF_PRODUCT = NULL
				SET @SF_ODM = NULL
				SET @SF_MONTH = NULL
				SET @SF_QUARTER = NULL
				SET @SF_YEAR = NULL
				SET @SF_QUANTITY = NULL
				SET @SF_PRICE = NULL
				SET @SF_USERLIST1 = NULL
				SET @SF_USERDATE1 = NULL
				SET @SF_USERTXT1 = NULL
				SET @SF_USERTXT2 = NULL
				SET @SF_CONFIDENCE_LEVEL = NULL
				SET @SF_FORECAST_TYPE = NULL	
				SET @MKTG_PART_NUMBER = NULL			
	
 				FETCH NEXT FROM FORECASTCURSOR
 				INTO @SF_BUILDNO, @SF_SPID, @SF_CUSTOMER, @SF_PROJECT, @SF_PRODUCT, @SF_ODM, @SF_MONTH, @SF_QUARTER, @SF_YEAR, @SF_QUANTITY, @SF_PRICE, @SF_USERLIST1, @SF_USERDATE1, @SF_USERTXT1, @SF_USERTXT2, @SF_CONFIDENCE_LEVEL, @SF_FORECAST_TYPE				
 			END	

 		CLOSE FORECASTCURSOR
 		--DEALLOCATE FORECASTCURSOR 
		
		IF @REPORT_ON IS NOT NULL AND LTRIM(RTRIM(@REPORT_ON)) = 'SALES'
		BEGIN	
			OPEN SPERSON_CURSOR 
	 			FETCH NEXT FROM SPERSON_CURSOR
	 			INTO @SPERSON_SPID, @LEVEL_ORDER
	 			WHILE @@FETCH_STATUS = 0
	 			BEGIN
	
					INSERT INTO @SPERSON_RESULTS SELECT * FROM @RESULTS WHERE SPERSON_ID = @SPERSON_SPID
					
					SET @SPERSON_SPID = NULL 										
	 				FETCH NEXT FROM SPERSON_CURSOR
	 				INTO @SPERSON_SPID, @LEVEL_ORDER
	 			END	
 				--DELETE FROM @RESULTS
				--INSERT INTO @RESULTS SELECT * FROM @SPERSON_RESULTS
				INSERT INTO @TOTAL_RESULTS SELECT  SPERSON_ID, CUSTOMER, PROJECT, PRODUCT, ODM, MONTH, QUARTER, YEAR, SUM(FC_QUANTITY) FC_QUANTITY, SUM(FC_PRICE) FC_PRICE, AVG(CONFIDENCE_LEVEL) CONFIDENCE_LEVEL, CUSTID, PROJID, PRODID, ODMID, FORECAST_TYPE, SALES_PERSON, QUARTER_START, QUARTER_END FROM @SPERSON_RESULTS				
				GROUP BY SPERSON_ID, CUSTOMER, CUSTID, PROJECT, PROJID, PRODUCT, PRODID, ODM, ODMID, FORECAST_TYPE, SALES_PERSON, MONTH, QUARTER, QUARTER_START, QUARTER_END, YEAR
				IF @TYPE IS NOT NULL AND LTRIM(RTRIM(@TYPE)) = 'BYPRODUCT'
				BEGIN
					INSERT INTO @TEMP_RESULTS SELECT NULL, NULL, NULL, PRODUCT, NULL, MONTH, NULL, YEAR, SUM(FC_QUANTITY) FC_QUANTITY, SUM(FC_PRICE) FC_PRICE, AVG(CONFIDENCE_LEVEL) CONFIDENCE_LEVEL, NULL, NULL, PRODID, NULL, NULL, NULL, NULL, NULL FROM @TOTAL_RESULTS				
					GROUP BY PRODUCT, PRODID, MONTH, YEAR						
				END
				ELSE IF @TYPE IS NOT NULL AND LTRIM(RTRIM(@TYPE)) = 'BYENDCUST'
				BEGIN
					INSERT INTO @TEMP_RESULTS SELECT NULL, NULL, NULL, NULL, ODM, MONTH, NULL, YEAR, SUM(FC_QUANTITY) FC_QUANTITY, SUM(FC_PRICE) FC_PRICE, AVG(CONFIDENCE_LEVEL) CONFIDENCE_LEVEL, NULL, NULL, NULL, ODMID, NULL, NULL, NULL, NULL FROM @TOTAL_RESULTS				
					GROUP BY ODM, ODMID, MONTH, YEAR
				END	
				DELETE FROM @RESULTS
				INSERT INTO @RESULTS SELECT NULL, SPERSON_ID, CUSTOMER, PROJECT, PRODUCT, ODM, MONTH, QUARTER, YEAR, FC_QUANTITY, FC_PRICE,NULL, NULL, NULL, NULL, CONFIDENCE_LEVEL, NULL, NULL, NULL, NULL, CUSTID, PROJID, PRODID, ODMID, FORECAST_TYPE, SALES_PERSON, QUARTER_START, QUARTER_END FROM @TEMP_RESULTS																								
				
				--UPDATE @RESULTS SET ODM = '' WHERE ODM IS NULL

	 		CLOSE SPERSON_CURSOR
	 		DEALLOCATE SPERSON_CURSOR 
			
		END
	

		-- FOR BACKLOG DETAILS


		DECLARE @CURRENT_DATE DATETIME
		SET @CURRENT_DATE = CONVERT(DATETIME,CURRENT_TIMESTAMP)
		DECLARE @BL_CUSTOMER VARCHAR(100)
		DECLARE @BL_END_USER VARCHAR(100)
	 	DECLARE @BL_PRODUCT VARCHAR(50)
		DECLARE @BL_MONTH INT
		DECLARE @BL_QUARTER INT
	 	DECLARE @BL_YEAR INT
	 	DECLARE @BL_QUANTITY INT
		DECLARE @OLD_QUANTITY INT
		DECLARE @BL_PRICE FLOAT
		DECLARE @OLD_PRICE FLOAT
		DECLARE @ROW_COUNT INT
		DECLARE @FC_CUSTOMER VARCHAR(100)
		DECLARE @BL_SALES_PERSON VARCHAR(100)
		DECLARE @BL_REQ_OR_SCH_DATE DATETIME
		DECLARE @BL_AMOUNT FLOAT
		DECLARE @BL_STATUS  VARCHAR(50)
		DECLARE @LINEITEM_ID INT
		DECLARE @REVISED_DATE DATETIME
		DECLARE @SHIPPED_QTY INT
		SET @QUARTER_START = NULL
		SET @QUARTER_END = NULL

		DECLARE @BACKLOGSTRING NVARCHAR(3000)
		DECLARE @BACKLOGPARAMS NVARCHAR(500)

		SET @BACKLOGSTRING = N'DECLARE BL_CURSOR CURSOR FOR SELECT C.C_NAME, P.PART_NUMBER, ISNULL( COOL.COOL_SCHEDULE_DATE, CURRENT_TIMESTAMP ) as COOL_SCHEDULE_DATE, COOL.COOL_AMOUNT AS AMOUNT, COOL.COOL_STATUS, LINEITEM_ID, 
		 			COOL_REVISED_DATE, COOL_QTY, COOL_UNIT_COST, CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID = -1 OR SO.SO_ODM_ID = 0 OR SO.SO_ODM_ID = '''' ) THEN C.C_Name
					ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ODM_ID )
					END AS END_USER, SO.SO_SALES_PERSON AS SALES_PERSON
		 			FROM PRODUCT P,CUSTOMER_OPEN_ORDER_LINEITEM COOL,SALES_ORDER SO,CUSTOMER C WHERE COOL.PRODUCT_ID=P.PRODUCT_ID 
					AND COOL.SALESORDER_ID=SO.SALESORDER_ID AND SO.CUSTOMER_ID=C.CUSTOMER_ID
					AND SO.SO_IS_SAMPLE = 1 AND COOL_STATUS <> ''SHIPPED'''

		SET @BACKLOGPARAMS = N'@PRM_PRODUCT_INPUT VARCHAR(100), @PRM_ODM_INPUT VARCHAR(100), @PRM_SPERSON_INPUT VARCHAR(100), @START_MONTH INT, @PRM_CUSTOMER_INPUT VARCHAR(100)'
		
		IF @PRODUCT_INPUT <> '0'
		BEGIN
			--SET @BACKLOGSTRING = @BACKLOGSTRING + ' AND PART_NUMBER = @PRM_PRODUCT_INPUT '
			SET @BACKLOGSTRING = @BACKLOGSTRING + ' AND PART_NUMBER IN (SELECT PART_NUMBER FROM #CHIPSET_PARTS_TABLE) '
		END
		IF @ODM_INPUT <> '0'
		BEGIN
			SET @BACKLOGSTRING = @BACKLOGSTRING + ' AND (CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID = -1 OR SO.SO_ODM_ID = 0 OR SO.SO_ODM_ID = '''' ) THEN C.C_Name
					ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ODM_ID )
					END) = @PRM_ODM_INPUT '
		END
		IF @SPERSON_INPUT <> '0'
		BEGIN
			SET @BACKLOGSTRING = @BACKLOGSTRING + ' AND SO_SALES_PERSON = @PRM_SPERSON_INPUT '
		END	
		IF @CUSTOMER_INPUT <> '0'
		BEGIN
			SET @BACKLOGSTRING = @BACKLOGSTRING + ' AND C_NAME = @PRM_CUSTOMER_INPUT '
		END	

		EXECUTE SP_EXECUTESQL @BACKLOGSTRING, @BACKLOGPARAMS, @PRM_PRODUCT_INPUT = @PART_NUMBER, @PRM_ODM_INPUT = @END_CUSTOMER,  @PRM_SPERSON_INPUT = @SALES_PERSON, @START_MONTH = @START_MONTH, @PRM_CUSTOMER_INPUT = @CUSTOMER
		
 			OPEN BL_CURSOR 
 				FETCH NEXT FROM BL_CURSOR
 				--INTO @BL_CUSTOMER, @BL_PRODUCT, @BL_MONTH, @BL_QUARTER, @BL_YEAR, @BL_QUANTITY, @BL_PRICE, @BL_END_USER, @BL_SALES_PERSON
 				INTO @BL_CUSTOMER, @BL_PRODUCT, @BL_REQ_OR_SCH_DATE, @BL_AMOUNT, @BL_STATUS, @LINEITEM_ID, @REVISED_DATE, @BL_QUANTITY, @BL_PRICE, @BL_END_USER, @BL_SALES_PERSON  
 				WHILE @@FETCH_STATUS = 0
 				BEGIN	

					IF(@REVISED_DATE IS NOT NULL OR @REVISED_DATE <> '')
					BEGIN
						SET @BL_REQ_OR_SCH_DATE = @REVISED_DATE
						
						IF( @BL_REQ_OR_SCH_DATE < @CURRENT_DATE )
						BEGIN
							SET @BL_REQ_OR_SCH_DATE = @CURRENT_DATE
						END
						ELSE
						BEGIN
							SET @BL_REQ_OR_SCH_DATE = @BL_REQ_OR_SCH_DATE
						END
					END						
					ELSE
					BEGIN
						IF( @BL_REQ_OR_SCH_DATE < @CURRENT_DATE )
						BEGIN
							SET @BL_REQ_OR_SCH_DATE = @CURRENT_DATE
						END
						ELSE
						BEGIN
							SET @BL_REQ_OR_SCH_DATE = @BL_REQ_OR_SCH_DATE
						END
					END							
										
						/*IF (@CATEGORY = 'REQUESTED')                
						BEGIN
							
							IF( @BL_REQ_OR_SCH_DATE < @CURRENT_DATE )
							BEGIN
								SET @BL_REQ_OR_SCH_DATE = @CURRENT_DATE
							END
							ELSE
							BEGIN
								SET @BL_REQ_OR_SCH_DATE = @BL_REQ_OR_SCH_DATE
							END
						END	
						
						IF (@CATEGORY = 'SCHEDULE')
						BEGIN
							IF(@BL_REQ_OR_SCH_DATE IS NULL OR @BL_REQ_OR_SCH_DATE= '')
							BEGIN
								
								SET @BL_REQ_OR_SCH_DATE = @CURRENT_DATE
							END
							ELSE
							BEGIN
								SET @BL_REQ_OR_SCH_DATE = @BL_REQ_OR_SCH_DATE
						
								IF( @BL_REQ_OR_SCH_DATE < @CURRENT_DATE )
								BEGIN
									SET @BL_REQ_OR_SCH_DATE = @CURRENT_DATE
								END
								ELSE
								BEGIN
									SET @BL_REQ_OR_SCH_DATE = @BL_REQ_OR_SCH_DATE
								END
							END
						END
						
						IF (@CATEGORY = 'SCHEDULE')
						BEGIN
							IF(@REVISED_DATE IS NOT NULL OR @REVISED_DATE <> '')
							BEGIN
								SET @BL_REQ_OR_SCH_DATE = @REVISED_DATE
								
								IF( @BL_REQ_OR_SCH_DATE < @CURRENT_DATE )
								BEGIN
									SET @BL_REQ_OR_SCH_DATE = @CURRENT_DATE
								END
								ELSE
								BEGIN
									SET @BL_REQ_OR_SCH_DATE = @BL_REQ_OR_SCH_DATE
								END
						
								
							END
						END*/
						

						SET @BL_MONTH = DATEPART(MM,@BL_REQ_OR_SCH_DATE)-1
						SET @BL_YEAR  = YEAR(@BL_REQ_OR_SCH_DATE)

								IF ( @BL_QUANTITY IS NULL)
								BEGIN
									SET @BL_QUANTITY = 0
								END
								IF ( @BL_PRICE IS NULL)
								BEGIN
									SET @BL_PRICE = 0.0
								END
							
								IF(@BL_AMOUNT IS NULL)
								BEGIN
									SET @BL_AMOUNT = 0
								END
								ELSE
								BEGIN
									SET @BL_AMOUNT = @BL_QUANTITY * @BL_PRICE
								END				
								
								SELECT @SHIPPED_QTY = ISNULL(SUM(CSLIT_QTY_SHIPPED),0) FROM CUSTOMER_SHIPPING_LINE_ITEM_TABLE WHERE LINEITEM_ID = @LINEITEM_ID AND CSLIT_STATUS = 'SHIPPED'
								
								SET @BL_AMOUNT = @BL_AMOUNT - (@BL_PRICE * @SHIPPED_QTY) 
								
								IF ( @BL_AMOUNT < 0 )
								BEGIN
									SET @BL_AMOUNT = 0 
								END
	
								SET @BL_QUANTITY = @BL_QUANTITY - @SHIPPED_QTY
								IF ( @BL_QUANTITY < 0 )
								BEGIN
									SET @BL_QUANTITY = 0
								END

	 					SELECT @ROW_COUNT = COUNT(*) FROM @BL_RESULTS
	 					WHERE CUSTOMER = @BL_CUSTOMER AND PRODUCT = @BL_PRODUCT AND ODM = @BL_END_USER AND MONTH = @BL_MONTH AND YEAR = @BL_YEAR 
	 
	 						IF @ROW_COUNT = 0 
	 					        BEGIN
								IF (@BL_MONTH >=0 AND @BL_MONTH <=2)
					  			BEGIN
					  				SET @QUARTER_START = 1
					  				SET @QUARTER_END =3
					  			END
					  			ELSE IF (@BL_MONTH >=3 AND @BL_MONTH <=5)
					  			BEGIN
					  				SET @QUARTER_START = 2
					  				SET @QUARTER_END =6
					 			END
					  			ELSE IF (@BL_MONTH >=6 AND @BL_MONTH <=8)
					  			BEGIN
					  				SET @QUARTER_START = 3
					  				SET @QUARTER_END =9
					  			END						
					  			ELSE IF (@BL_MONTH >=9 AND @BL_MONTH <=11)
					  			BEGIN
					  				SET @QUARTER_START = 4
					  				SET @QUARTER_END =12
					   			END
									/*							
								IF ( @BL_QUANTITY IS NULL)
								BEGIN
									SET @BL_QUANTITY = 0
								END
								IF ( @BL_PRICE IS NULL)
								BEGIN
									SET @BL_PRICE = 0.0
								END
							
								IF(@BL_AMOUNT IS NULL)
								BEGIN
									SET @BL_AMOUNT = 0
								END
								ELSE
								BEGIN
									SET @BL_AMOUNT = @BL_QUANTITY * @BL_PRICE
								END				
								
								SELECT @SHIPPED_QTY = ISNULL(SUM(CSLIT_QTY_SHIPPED),0) FROM CUSTOMER_SHIPPING_LINE_ITEM_TABLE WHERE LINEITEM_ID = @LINEITEM_ID AND CSLIT_STATUS = 'SHIPPED'
								
								SET @BL_AMOUNT = @BL_AMOUNT - (@BL_PRICE * @SHIPPED_QTY) 
								
								IF ( @BL_AMOUNT < 0 )
								BEGIN
									SET @BL_AMOUNT = 0 
								END
	
								SET @BL_QUANTITY = @BL_QUANTITY - @SHIPPED_QTY
								IF ( @BL_QUANTITY < 0 )
								BEGIN
									SET @BL_QUANTITY = 0
								END*/					
									

	 							INSERT INTO @BL_RESULTS VALUES( 0, @BL_CUSTOMER, '0', @BL_PRODUCT, @BL_END_USER, @BL_MONTH, @BL_QUARTER, @BL_YEAR, @BL_QUANTITY, @BL_AMOUNT, '0', '0', '0', '0', 0.0, @BL_SALES_PERSON, @QUARTER_START, @QUARTER_END )
	 						END
	 						ELSE
	 						BEGIN
	 							SELECT @OLD_QUANTITY = BL_QUANTITY, @OLD_PRICE = BL_PRICE  FROM @BL_RESULTS
	 							WHERE CUSTOMER = @BL_CUSTOMER AND PRODUCT = @BL_PRODUCT AND ODM = @BL_END_USER AND MONTH = @BL_MONTH AND YEAR = @BL_YEAR 
	 							
	 	
	 							UPDATE @BL_RESULTS SET BL_QUANTITY = (@BL_QUANTITY + @OLD_QUANTITY), BL_PRICE = @OLD_PRICE +  ( @BL_QUANTITY*@BL_PRICE )
	 							WHERE CUSTOMER = @BL_CUSTOMER AND PRODUCT = @BL_PRODUCT AND ODM = @BL_END_USER  AND MONTH = @BL_MONTH AND YEAR = @BL_YEAR 
							END
							
							SET @BL_CUSTOMER = NULL
							SET @BL_PRODUCT = NULL
							SET @BL_MONTH = NULL
							SET @BL_QUARTER = NULL
							SET @BL_YEAR = NULL
							SET @BL_QUANTITY = NULL
							SET @BL_PRICE = NULL
							SET @BL_END_USER = NULL
							SET @BL_SALES_PERSON = NULL 	
							SET @BL_REQ_OR_SCH_DATE = NULL
							SET @BL_AMOUNT = NULL
							SET @BL_STATUS = NULL
							SET @LINEITEM_ID = NULL
							SET @REVISED_DATE = NULL
							SET @SHIPPED_QTY = NULL
							 			 														
					FETCH NEXT FROM BL_CURSOR
 					--INTO @BL_CUSTOMER, @BL_PRODUCT, @BL_MONTH, @BL_QUARTER, @BL_YEAR, @BL_QUANTITY, @BL_PRICE, @BL_END_USER, @BL_SALES_PERSON 
					INTO @BL_CUSTOMER, @BL_PRODUCT, @BL_REQ_OR_SCH_DATE, @BL_AMOUNT, @BL_STATUS, @LINEITEM_ID, @REVISED_DATE, @BL_QUANTITY, @BL_PRICE, @BL_END_USER, @BL_SALES_PERSON  
					
				END					
				IF @TYPE IS NOT NULL AND LTRIM(RTRIM(@TYPE)) = 'BYPRODUCT'
				BEGIN
					INSERT INTO @BL_TEMP_RESULTS SELECT NULL,NULL,NULL,PRODUCT,NULL,MONTH,NULL,YEAR,SUM(BL_QUANTITY),SUM(BL_PRICE),NULL,NULL,NULL,NULL,AVG(CONFIDENCE_LEVEL),NULL,NULL,NULL
					FROM @BL_RESULTS 
						WHERE CONVERT(DATETIME, CONVERT( VARCHAR, MONTH+1 )+'/01/'+ CONVERT( VARCHAR, YEAR )) >= @START_DATE
							AND CONVERT(DATETIME, CONVERT( VARCHAR, MONTH+1 )+'/01/'+ CONVERT( VARCHAR, YEAR )) <= @END_DATE
						GROUP BY PRODUCT,MONTH,YEAR
				END
				ELSE IF @TYPE IS NOT NULL AND LTRIM(RTRIM(@TYPE)) = 'BYENDCUST'
				BEGIN
					INSERT INTO @BL_TEMP_RESULTS SELECT NULL,NULL,NULL,NULL,ODM,MONTH,NULL,YEAR,SUM(BL_QUANTITY),SUM(BL_PRICE),NULL,NULL,NULL,NULL,AVG(CONFIDENCE_LEVEL),NULL,NULL,NULL
					FROM @BL_RESULTS 
						WHERE CONVERT(DATETIME, CONVERT( VARCHAR, MONTH+1 )+'/01/'+ CONVERT( VARCHAR, YEAR )) >= @START_DATE
							AND CONVERT(DATETIME, CONVERT( VARCHAR, MONTH+1 )+'/01/'+ CONVERT( VARCHAR, YEAR )) <= @END_DATE
						GROUP BY ODM,MONTH,YEAR
				END
 					
 			CLOSE BL_CURSOR
 			DEALLOCATE BL_CURSOR

/*			IF @TYPE IS NOT NULL AND LTRIM(RTRIM(@TYPE)) = 'BYPRODUCT'
			BEGIN	
				INSERT INTO @RESULTS SELECT  NULL, NULL, NULL, 'NO PROJECT', PRODUCT, NULL, MONTH, NULL, YEAR, NULL, NULL, NULL, NULL, NULL, NULL, NULL, BL_QUANTITY, BL_PRICE, NULL, NULL, NULL, NULL, NULL, NULL, 'SALES', NULL, NULL, NULL FROM @BL_TEMP_RESULTS
				WHERE CONVERT(DATETIME, CONVERT( VARCHAR, MONTH+1 )+'/01/'+ CONVERT( VARCHAR, YEAR )) >= @START_DATE
					AND CONVERT(DATETIME, CONVERT( VARCHAR, MONTH+1 )+'/01/'+ CONVERT( VARCHAR, YEAR )) <= @END_DATE

			END
			ELSE IF @TYPE IS NOT NULL AND LTRIM(RTRIM(@TYPE)) = 'BYENDCUST'
			BEGIN
				INSERT INTO @RESULTS SELECT  NULL, NULL, NULL, 'NO PROJECT', NULL, ODM, MONTH, NULL, YEAR, NULL, NULL, NULL, NULL, NULL, NULL, NULL, BL_QUANTITY, BL_PRICE, NULL, NULL, NULL, NULL, NULL, NULL, 'SALES', NULL, NULL, NULL FROM @BL_TEMP_RESULTS
				WHERE CONVERT(DATETIME, CONVERT( VARCHAR, MONTH+1 )+'/01/'+ CONVERT( VARCHAR, YEAR )) >= @START_DATE
					AND CONVERT(DATETIME, CONVERT( VARCHAR, MONTH+1 )+'/01/'+ CONVERT( VARCHAR, YEAR )) <= @END_DATE
			END
*/

	-- FOR SHIP DETAILS

	DECLARE @SHIP_CUSTOMER VARCHAR(100)
	DECLARE @SHIP_END_USER VARCHAR(100)
 	DECLARE @SHIP_PRODUCT VARCHAR(50)
	DECLARE @SHIP_SALES_PERSON VARCHAR(100)
	DECLARE @SHIP_MONTH INT
	DECLARE @SHIP_QUARTER INT
 	DECLARE @SHIP_YEAR INT
 	DECLARE @SHIP_QUANTITY INT
	DECLARE @SHIP_OLD_QUANTITY INT
	DECLARE @SHIP_PRICE FLOAT
	DECLARE @SHIP_OLD_PRICE FLOAT
	DECLARE @SHIP_ROW_COUNT INT	
	SET @QUARTER_START = NULL
	SET @QUARTER_END = NULL


	DECLARE @SHIPSTRING NVARCHAR(3000)
	DECLARE @SHIPPARAMS NVARCHAR(500)

	SET @SHIPSTRING = N'DECLARE SHIP_CURSOR CURSOR FOR SELECT CUSTOMER, PRODUCT, SALES_PERSON, END_USER, SHIPMONTH, SHIPQUARTER, SHIPYEAR, QUANTITY, UNIT_PRICE
	 		FROM FN_SHIPPED_ORDERS_FOR_FORECAST_REPORT( ''0'', ''0'') WHERE 1=1'
	SET @SHIPPARAMS = N'@PRM_PRODUCT_INPUT VARCHAR(100), @PRM_ODM_INPUT VARCHAR(100), @PRM_SPERSON_INPUT VARCHAR(100), @START_MONTH INT, @START_DATE DATETIME, @END_DATE DATETIME, @PRM_CUSTOMER_INPUT VARCHAR(100)'

	IF @PRODUCT_INPUT <> '0'
	BEGIN
		--SET @SHIPSTRING = @SHIPSTRING + ' AND PRODUCT = @PRM_PRODUCT_INPUT '
		SET @SHIPSTRING = @SHIPSTRING + ' AND PRODUCT IN (SELECT PART_NUMBER FROM #CHIPSET_PARTS_TABLE) '
	END
	IF @ODM_INPUT <> '0'
	BEGIN
		SET @SHIPSTRING = @SHIPSTRING + ' AND END_USER = @PRM_ODM_INPUT '
	END
	IF @SPERSON_INPUT <> '0'
	BEGIN
		SET @SHIPSTRING = @SHIPSTRING + ' AND SALES_PERSON = @PRM_SPERSON_INPUT '
	END	
	IF @CUSTOMER_INPUT <> '0'
	BEGIN
		SET @SHIPSTRING = @SHIPSTRING + ' AND CUSTOMER = @PRM_CUSTOMER_INPUT '
	END
			
	SET @SQLSTRING = @SQLSTRING + ' AND CONVERT(DATETIME, CONVERT( VARCHAR, SHIPMONTH+1 )+''/01/''+ CONVERT( VARCHAR, SHIPYEAR )) >= @START_DATE'
	SET @SQLSTRING = @SQLSTRING + ' AND CONVERT(DATETIME, CONVERT( VARCHAR, SHIPMONTH+1 )+''/01/''+ CONVERT( VARCHAR, SHIPYEAR )) <= @END_DATE'

	EXECUTE SP_EXECUTESQL @SHIPSTRING, @SHIPPARAMS, @PRM_PRODUCT_INPUT = @PART_NUMBER, @PRM_ODM_INPUT = @END_CUSTOMER,  @PRM_SPERSON_INPUT = @SALES_PERSON, @START_MONTH = @START_MONTH, @START_DATE = @START_DATE, @END_DATE = @END_DATE, @PRM_CUSTOMER_INPUT = @CUSTOMER

	 	OPEN SHIP_CURSOR	
	 		FETCH NEXT FROM SHIP_CURSOR
	 		INTO @SHIP_CUSTOMER, @SHIP_PRODUCT, @SHIP_SALES_PERSON, @SHIP_END_USER, @SHIP_MONTH, @SHIP_QUARTER, @SHIP_YEAR, @SHIP_QUANTITY, @SHIP_PRICE
	
			WHILE @@FETCH_STATUS = 0
 			BEGIN	
	
 					SELECT @SHIP_ROW_COUNT = COUNT(*) FROM @SHIP_RESULTS
 					WHERE CUSTOMER = @SHIP_CUSTOMER AND PRODUCT = @SHIP_PRODUCT AND ODM = @SHIP_END_USER AND MONTH = @SHIP_MONTH AND YEAR = @SHIP_YEAR 
 					
 						IF @SHIP_ROW_COUNT = 0 
 					        BEGIN
 							IF (@SHIP_MONTH >=0 AND @SHIP_MONTH <=2)
				  			BEGIN
				  				SET @QUARTER_START = 1
				  				SET @QUARTER_END =3
				  			END
				  			ELSE IF (@SHIP_MONTH >=3 AND @SHIP_MONTH <=5)
				  			BEGIN
				  				SET @QUARTER_START = 2
				  				SET @QUARTER_END =6
				 			END
				  			ELSE IF (@SHIP_MONTH >=6 AND @SHIP_MONTH <=8)
				  			BEGIN
				  				SET @QUARTER_START = 3
				  				SET @QUARTER_END =9
				  			END						
				  			ELSE IF (@SHIP_MONTH >=9 AND @SHIP_MONTH <=11)
				  			BEGIN
				  				SET @QUARTER_START = 4
				  				SET @QUARTER_END =12
				   			END
							
							INSERT INTO @SHIP_RESULTS VALUES( 0, @SHIP_CUSTOMER, '0', @SHIP_PRODUCT, @SHIP_END_USER, @SHIP_MONTH, 0 , @SHIP_YEAR, @SHIP_QUANTITY, @SHIP_QUANTITY*@SHIP_PRICE, '0', '0', '0', '0', 0.0, @SHIP_SALES_PERSON, @QUARTER_START, @QUARTER_END )
 						END
 						ELSE
 						BEGIN
 							SELECT @SHIP_OLD_QUANTITY = ACTUAL_QUANTITY, @SHIP_OLD_PRICE = ACTUAL_PRICE  FROM @SHIP_RESULTS
 							WHERE CUSTOMER = @SHIP_CUSTOMER AND PRODUCT = @SHIP_PRODUCT AND ODM = @SHIP_END_USER AND MONTH = @SHIP_MONTH AND YEAR = @SHIP_YEAR 
 							
 	
 							UPDATE @SHIP_RESULTS SET ACTUAL_QUANTITY = (@SHIP_QUANTITY + @SHIP_OLD_QUANTITY), ACTUAL_PRICE = @SHIP_OLD_PRICE +  ( @SHIP_QUANTITY*@SHIP_PRICE )
 							WHERE CUSTOMER = @SHIP_CUSTOMER AND PRODUCT = @SHIP_PRODUCT  AND ODM = @SHIP_END_USER AND MONTH = @SHIP_MONTH AND YEAR = @SHIP_YEAR 
						END
							
							SET @SHIP_CUSTOMER = NULL
							SET @SHIP_PRODUCT = NULL
							SET @SHIP_SALES_PERSON = NULL
							SET @SHIP_END_USER = NULL
							SET @SHIP_MONTH = NULL
							SET @SHIP_QUARTER = NULL
							SET @SHIP_YEAR = NULL
							SET @SHIP_QUANTITY = NULL
							SET @SHIP_PRICE = NULL
				FETCH NEXT FROM SHIP_CURSOR
	 			INTO @SHIP_CUSTOMER, @SHIP_PRODUCT, @SHIP_SALES_PERSON, @SHIP_END_USER, @SHIP_MONTH, @SHIP_QUARTER, @SHIP_YEAR, @SHIP_QUANTITY, @SHIP_PRICE
			END 			
			IF @TYPE IS NOT NULL AND LTRIM(RTRIM(@TYPE)) = 'BYPRODUCT'
			BEGIN
				INSERT INTO @SHIP_TEMP_RESULTS SELECT NULL,NULL,NULL,PRODUCT,NULL,MONTH,NULL,YEAR,SUM(ACTUAL_QUANTITY),SUM(ACTUAL_PRICE),NULL,NULL,NULL,NULL,AVG(CONFIDENCE_LEVEL),NULL,NULL,NULL
				FROM @SHIP_RESULTS 
					WHERE CONVERT(DATETIME, CONVERT( VARCHAR, MONTH+1 )+'/01/'+ CONVERT( VARCHAR, YEAR )) >= @START_DATE
						AND CONVERT(DATETIME, CONVERT( VARCHAR, MONTH+1 )+'/01/'+ CONVERT( VARCHAR, YEAR )) <= @END_DATE
					GROUP BY PRODUCT,MONTH,YEAR
			END
			ELSE IF @TYPE IS NOT NULL AND LTRIM(RTRIM(@TYPE)) = 'BYENDCUST'
			BEGIN
				INSERT INTO @SHIP_TEMP_RESULTS SELECT NULL,NULL,NULL,NULL,ODM,MONTH,NULL,YEAR,SUM(ACTUAL_QUANTITY),SUM(ACTUAL_PRICE),NULL,NULL,NULL,NULL,AVG(CONFIDENCE_LEVEL),NULL,NULL,NULL
				FROM @SHIP_RESULTS 
					WHERE CONVERT(DATETIME, CONVERT( VARCHAR, MONTH+1 )+'/01/'+ CONVERT( VARCHAR, YEAR )) >= @START_DATE
						AND CONVERT(DATETIME, CONVERT( VARCHAR, MONTH+1 )+'/01/'+ CONVERT( VARCHAR, YEAR )) <= @END_DATE
					GROUP BY ODM,MONTH,YEAR
			END

	 	CLOSE SHIP_CURSOR
	 	DEALLOCATE SHIP_CURSOR	
	
/*		IF @TYPE IS NOT NULL AND LTRIM(RTRIM(@TYPE)) = 'BYPRODUCT'
		BEGIN	
			INSERT INTO @RESULTS SELECT  NULL, NULL, NULL, 'NO PROJECT', PRODUCT, NULL, MONTH, NULL, YEAR, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ACTUAL_QUANTITY, ACTUAL_PRICE, NULL, NULL, NULL, NULL, 'SALES', NULL, NULL, NULL FROM @SHIP_TEMP_RESULTS
			WHERE CONVERT(DATETIME, CONVERT( VARCHAR, MONTH+1 )+'/01/'+ CONVERT( VARCHAR, YEAR )) >= @START_DATE
				AND CONVERT(DATETIME, CONVERT( VARCHAR, MONTH+1 )+'/01/'+ CONVERT( VARCHAR, YEAR )) <= @END_DATE

		END
		ELSE IF @TYPE IS NOT NULL AND LTRIM(RTRIM(@TYPE)) = 'BYENDCUST'
		BEGIN
			INSERT INTO @RESULTS SELECT  NULL, NULL, NULL, 'NO PROJECT', NULL, ODM, MONTH, NULL, YEAR, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ACTUAL_QUANTITY, ACTUAL_PRICE, NULL, NULL, NULL, NULL, 'SALES', NULL, NULL, NULL FROM @SHIP_TEMP_RESULTS
			WHERE CONVERT(DATETIME, CONVERT( VARCHAR, MONTH+1 )+'/01/'+ CONVERT( VARCHAR, YEAR )) >= @START_DATE
				AND CONVERT(DATETIME, CONVERT( VARCHAR, MONTH+1 )+'/01/'+ CONVERT( VARCHAR, YEAR )) <= @END_DATE
		END
*/

	--Updating primary part number with the chipset part number if it is belogs.
	SET @SF_PRODUCT = NULL
	OPEN FORECASTCURSOR 
	FETCH NEXT FROM FORECASTCURSOR
	INTO @SF_BUILDNO, @SF_SPID, @SF_CUSTOMER, @SF_PROJECT, @SF_PRODUCT, @SF_ODM, @SF_MONTH, @SF_QUARTER, @SF_YEAR, @SF_QUANTITY, @SF_PRICE, @SF_USERLIST1, @SF_USERDATE1, @SF_USERTXT1, @SF_USERTXT2, @SF_CONFIDENCE_LEVEL, @SF_FORECAST_TYPE
	WHILE @@FETCH_STATUS = 0
	BEGIN

		SET @SF_PRODUCT_NAME = NULL
		
		SELECT @SF_PRODUCT_NAME = PART_NUMBER, @PROCESSSTEP_ID = PROCESSSTEP_ID FROM PRODUCT WHERE PRODUCT_ID = @SF_PRODUCT
		--check if it is a chipset part number or not					
		IF((SELECT COUNT(*) FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND PARAMETER_KEY = 'IsChipset' AND PARAMETER_VALUE = 'true') > 0)
		 BEGIN
			--it is a chipset part number, so update the primary part of the chipset with the chipset part number
			SELECT @PRIMARY_PART_NUMBER = P.PART_NUMBER FROM Assembly_BOM_Setup_Table ABST, PRODUCT P WHERE ABST.ASSEMBLY_PROCESSSTEP_ID = @PROCESSSTEP_ID AND ABST.IS_PRIMARY_PART = 0 AND P.PROCESSSTEP_ID = ABST.BOM_PROCESSSTEP_ID
			DELETE FROM #CHIPSET_PARTS_TABLE
			INSERT INTO #CHIPSET_PARTS_TABLE(PRODUCT_ID, PART_NUMBER) SELECT P.PRODUCT_ID, P.PART_NUMBER FROM Assembly_BOM_Setup_Table ABST, PRODUCT P WHERE ABST.ASSEMBLY_PROCESSSTEP_ID = @PROCESSSTEP_ID AND P.PROCESSSTEP_ID = ABST.BOM_PROCESSSTEP_ID

			IF(@PRIMARY_PART_NUMBER IS NOT NULL)
			 BEGIN				
				--update the primary part revenue to primary part backlog qty * (sum of chipset parts unit price)
				SELECT @CHIPSET_UNIT_PRICE = SUM(BL_PRICE/BL_QUANTITY) FROM @BL_TEMP_RESULTS 
					WHERE PRODUCT IN (SELECT PART_NUMBER FROM #CHIPSET_PARTS_TABLE) AND MONTH = @SF_MONTH AND YEAR = @SF_YEAR
						
				UPDATE @BL_TEMP_RESULTS SET BL_PRICE = BL_QUANTITY * ISNULL(@CHIPSET_UNIT_PRICE,0) 
					WHERE PRODUCT = @PRIMARY_PART_NUMBER AND MONTH = @SF_MONTH AND YEAR = @SF_YEAR

				--delete non primary parts from backlog table as we don't need to consider now
				DELETE FROM @BL_TEMP_RESULTS WHERE PRODUCT IN (SELECT PART_NUMBER FROM #CHIPSET_PARTS_TABLE) 
												AND PRODUCT != @PRIMARY_PART_NUMBER AND MONTH = @SF_MONTH AND YEAR = @SF_YEAR
				--update primary part with chipset part number
				UPDATE @BL_TEMP_RESULTS SET PRODUCT = @SF_PRODUCT_NAME WHERE PRODUCT = @PRIMARY_PART_NUMBER AND MONTH = @SF_MONTH AND YEAR = @SF_YEAR
				
				--update the primary part revenue to primary part ship qty * (sum of chipset parts unit price)
				SELECT @CHIPSET_UNIT_PRICE = SUM(ACTUAL_PRICE/ACTUAL_QUANTITY) FROM @SHIP_TEMP_RESULTS 
							WHERE PRODUCT IN (SELECT PART_NUMBER FROM #CHIPSET_PARTS_TABLE) AND MONTH = @SF_MONTH AND YEAR = @SF_YEAR

				UPDATE @SHIP_TEMP_RESULTS SET ACTUAL_PRICE = ACTUAL_QUANTITY * ISNULL(@CHIPSET_UNIT_PRICE,0) 
							WHERE PRODUCT = @PRIMARY_PART_NUMBER AND MONTH = @SF_MONTH AND YEAR = @SF_YEAR

				--delete non primary parts from ship table as we don't need to consider now
				DELETE FROM @SHIP_TEMP_RESULTS WHERE PRODUCT IN (SELECT PART_NUMBER FROM #CHIPSET_PARTS_TABLE) 
													AND PRODUCT != @PRIMARY_PART_NUMBER AND MONTH = @SF_MONTH AND YEAR = @SF_YEAR
				--update primary part with chipset part number
				UPDATE @SHIP_TEMP_RESULTS SET PRODUCT = @SF_PRODUCT_NAME WHERE PRODUCT = @PRIMARY_PART_NUMBER AND MONTH = @SF_MONTH AND YEAR = @SF_YEAR
				
				--UPDATE @RESULTS SET PRODUCT = @SF_PRODUCT_NAME, FC_QUANTITY = NULL, FC_PRICE = NULL, USERLIST1 = NULL, USERDATE1 = NULL, USERTXT1 = NULL, USERTXT2 = NULL, CONFIDENCE_LEVEL = NULL WHERE PRODUCT = @PRIMARY_PART_NUMBER				
			 END
			/*ELSE --IF NO PRIMARY PART NUMBER EXISTS
			 BEGIN
				--WHAT TO DO???
			 END*/
		 END

		SET @PRIMARY_PART_NUMBER = NULL
		FETCH NEXT FROM FORECASTCURSOR
		INTO @SF_BUILDNO, @SF_SPID, @SF_CUSTOMER, @SF_PROJECT, @SF_PRODUCT, @SF_ODM, @SF_MONTH, @SF_QUARTER, @SF_YEAR, @SF_QUANTITY, @SF_PRICE, @SF_USERLIST1, @SF_USERDATE1, @SF_USERTXT1, @SF_USERTXT2, @SF_CONFIDENCE_LEVEL, @SF_FORECAST_TYPE				
	END	
 		 																
 	CLOSE FORECASTCURSOR

	INSERT INTO @RESULTS SELECT  NULL, NULL, NULL, 'NO PROJECT', PRODUCT, ODM, MONTH, NULL, YEAR, NULL, NULL, NULL, NULL, NULL, NULL, NULL, BL_QUANTITY, BL_PRICE, NULL, NULL, NULL, NULL, NULL, NULL, 'SALES', NULL, NULL, NULL FROM @BL_TEMP_RESULTS
	INSERT INTO @RESULTS SELECT  NULL, NULL, NULL, 'NO PROJECT', PRODUCT, ODM, MONTH, NULL, YEAR, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ACTUAL_QUANTITY, ACTUAL_PRICE, NULL, NULL, NULL, NULL, 'SALES', NULL, NULL, NULL FROM @SHIP_TEMP_RESULTS

	-- Updation of part number with corresponding marketing part number
	IF @PART_NUMBER_TYPE <> 1
	BEGIN	
		SET @SF_PRODUCT = NULL
		OPEN FORECASTCURSOR 
	 			FETCH NEXT FROM FORECASTCURSOR
	 			INTO @SF_BUILDNO, @SF_SPID, @SF_CUSTOMER, @SF_PROJECT, @SF_PRODUCT, @SF_ODM, @SF_MONTH, @SF_QUARTER, @SF_YEAR, @SF_QUANTITY, @SF_PRICE, @SF_USERLIST1, @SF_USERDATE1, @SF_USERTXT1, @SF_USERTXT2, @SF_CONFIDENCE_LEVEL, @SF_FORECAST_TYPE
	 			WHILE @@FETCH_STATUS = 0
	 			BEGIN
	
					SET @SF_PRODUCT_NAME = NULL
					
					SELECT @SF_PRODUCT_NAME = PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = @SF_PRODUCT

					SELECT @MKTG_PART_NUMBER = PRODUCT.P_MARKETING_PART_NUMBER FROM PRODUCT,PROCESS_STEP  WHERE PRODUCT.PRODUCT_ID = @SF_PRODUCT AND PRODUCT.PROCESSSTEP_ID IN (SELECT PROCESSSTEP_ID FROM PROCESS_STEP WHERE PS_TYPE_NAME LIKE 'FINISHED GOODS') AND PRODUCT.PROCESSSTEP_ID=PROCESS_STEP.PROCESSSTEP_ID
					UPDATE @RESULTS SET PRODUCT =  @MKTG_PART_NUMBER WHERE PRODUCT = @SF_PRODUCT_NAME
	 					
					SET @MKTG_PART_NUMBER = NULL
	 				FETCH NEXT FROM FORECASTCURSOR
	 				INTO @SF_BUILDNO, @SF_SPID, @SF_CUSTOMER, @SF_PROJECT, @SF_PRODUCT, @SF_ODM, @SF_MONTH, @SF_QUARTER, @SF_YEAR, @SF_QUANTITY, @SF_PRICE, @SF_USERLIST1, @SF_USERDATE1, @SF_USERTXT1, @SF_USERTXT2, @SF_CONFIDENCE_LEVEL, @SF_FORECAST_TYPE				
	 			END	
	 		 																
	 	CLOSE FORECASTCURSOR
	END

 	DEALLOCATE FORECASTCURSOR 

	--UPDATE @RESULTS SET ODM = NULL WHERE ODM = ''	
	
	SELECT * FROM @RESULTS


 END
 
 







